﻿Module modCommon
    Public dbConn As New OleDb.OleDbConnection
    Public dbConnSQL As New OleDb.OleDbConnection
    Public dbSrc As String
    Public prdCdChangeActionOn As Boolean '## 콤보박스 변경시 값 로딩 여부 결정
    Public gSeqsOnBill As String = ""           '## 거래명세서 발행을 위해 필요
    Public gAmountOnBill As Double = 0       '## 거래명세서 발행을 위해 필요
    Public gCompCdOnBill As Integer = 0      '## 거래명세서 발행을 위해 필요
    Public gSessionKey As String = ""            '## 출고 정보를 위한 세션키
    Public gVersion As String = "Ver. 0.059"

    Public Sub dbConnection()
        dbSrc = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & Application.StartupPath & "\data\dbInventory.MDB"
        'MsgBox(dbSrc)
        dbConn.ConnectionString = dbSrc
        dbConn.Open()
    End Sub
    Public Sub dbSqlConnection()
        dbSrc = "provider=sqloledb;server=inventory.jadesoft.co.kr;database=noahtech;uid=noahtech;pwd=shdkxpr7;"
        'MsgBox(dbSrc)
        dbConnSQL.ConnectionString = dbSrc
        dbConnSQL.Open()
    End Sub

    '## 문자열에 '체크하여 처리
    Public Function gFnChkVal(val As String) As String
        If val <> "" Then
            val = val.Replace("'", "''")
        End If
        Return val
    End Function

    '## 실시간 코마 찍기
    Public Sub gSubWithComma(ByVal obj As Object)
        Try
            If obj.text <> "" Then
                obj.Text = Format(CInt(obj.Text), "#,##0")
                obj.SelectionStart = obj.Text.Length
            End If
        Catch err As OverflowException
            'MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 삭제 여부 콤보박스 만들기
    Public Sub gSubMkCmbFlag(ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter

        'Call dbConnection()

        sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0002' ORDER BY itemSeq"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbFlag")

        dbConn.Close()
        With obj
            .DataSource = dsCommon.Tables("cmbFlag")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
            .SelectedIndex = 0
        End With
    End Sub

    '## 거래처/회원 유효 여부 콤보박스 만들기
    Public Sub gSubMkCmbMemFlag(ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter

        Call dbConnection()

        sql = "SELECT itemCd, itemNm FROM tblComCode WHERE flag='Y' AND groupCd='0011' ORDER BY itemSeq"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbMemFlag")

        dbConn.Close()
        With obj
            .DataSource = dsCommon.Tables("cmbMemFlag")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
            .SelectedIndex = 0
        End With
    End Sub

    '## 거래처 가져오기
    Public Sub gSubMkCmbCompany(ByVal val As String, ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim addSql As String = ""

        'Call dbConnection()

        If val <> "" Then
            addSql = "WHERE company LIKE '%" + val + "%' AND flag='Y' "
        End If
        sql = "SELECT company+'('+compNo+')' as compInfo, compCd FROM tblCompany " + addSql + " ORDER BY company"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "comp")

        dbConn.Close()
        Dim newDsRow As DataRow = dsCommon.Tables("comp").NewRow
        newDsRow("compInfo") = "-거래처-"
        newDsRow("compCd") = "0"

        dsCommon.Tables("comp").Rows.Add(newDsRow)

        Try
            With obj
                .DataSource = dsCommon.Tables("comp")
                .DisplayMember = "compInfo"
                .ValueMember = "compCd"
                '.SelectedIndex = 0
            End With
            If addSql = "" Then
                obj.SelectedIndex = dsCommon.Tables("comp").Rows.Count - 1
            End If
        Catch err As NullReferenceException
            MsgBox(err.Message)
        End Try
    End Sub

    '## 계정 정보 가져오기
    Public Sub gSubMkCmbAccount(ByVal obj As Object, ByVal flag As String)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim addSql As String = ""
        sql = "SELECT accountNm, accountCd FROM tblAccount WHERE flag='Y' AND [part]='" + flag + "' ORDER BY accountNm"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbAccGroup")

        dbConn.Close()
        Dim newDsRow As DataRow = dsCommon.Tables("cmbAccGroup").NewRow
        newDsRow("accountNm") = "-계정-"
        newDsRow("accountCd") = "0"

        dsCommon.Tables("cmbAccGroup").Rows.Add(newDsRow)
        With obj
            .DataSource = dsCommon.Tables("cmbAccGroup")
            .DisplayMember = "accountNm"
            .ValueMember = "accountCd"
        End With
        obj.SelectedIndex = dsCommon.Tables("cmbAccGroup").Rows.Count - 1
    End Sub

    '## 제품 가져오기
    Public Sub gSubMkCmbProduct(ByVal val As String, ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim addSql As String = ""
        prdCdChangeActionOn = False
        'Call dbConnection()
        If val <> "" Then
            prdCdChangeActionOn = True
            addSql = "WHERE prdNm LIKE '%" + val + "%' AND flag='Y' "
        End If
        sql = "SELECT prdNm as product, prdCd FROM tblProduct " + addSql + " ORDER BY prdNm"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "PRD")
        dbConn.Close()

        Dim newDsRow As DataRow = dsCommon.Tables("PRD").NewRow
        newDsRow("product") = "-제품-"
        newDsRow("prdCd") = "0"

        dsCommon.Tables("PRD").Rows.Add(newDsRow)

        Try
            With obj
                .DataSource = dsCommon.Tables("PRD")
                .DisplayMember = "product"
                .ValueMember = "prdCd"
                '.SelectedIndex = 0
            End With
            If addSql = "" Then
                obj.SelectedIndex = dsCommon.Tables("PRD").Rows.Count - 1
            End If
        Catch err As NullReferenceException
            MsgBox(err.Message)
        End Try
        prdCdChangeActionOn = True
    End Sub

    '## 결재 수단 
    Public Sub gSubMkCmbPayMethod(ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        'Call dbConnection()
        sql = "SELECT itemNm, itemCd FROM tblComCode WHERE flag='Y' AND groupCd='0006' ORDER BY itemSeq"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbPayMethod")

        dbConn.Close()
        Dim newDsRow As DataRow = dsCommon.Tables("cmbPayMethod").NewRow
        newDsRow("itemNm") = "-결재방법-"
        newDsRow("itemCd") = "0"

        dsCommon.Tables("cmbPayMethod").Rows.Add(newDsRow)
        With obj
            .DataSource = dsCommon.Tables("cmbPayMethod")
            .DisplayMember = "itemNm"
            .ValueMember = "itemCd"
        End With
        obj.SelectedIndex = dsCommon.Tables("cmbPayMethod").Rows.Count - 1
    End Sub

    '## 자료 보유 년도
    Public Sub gSubMkCmbYear(ByVal obj As Object)
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        'Call dbConnection()
        sql = "SELECT Yr FROM tblStatistics GROUP BY Yr ORDER BY Yr DESC"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "cmbYear")

        dbConn.Close()
        Dim newDsRow As DataRow = dsCommon.Tables("cmbYear").NewRow
        newDsRow("Yr") = "-전체-"

        dsCommon.Tables("cmbYear").Rows.Add(newDsRow)
        With obj
            .DataSource = dsCommon.Tables("cmbYear")
            .DisplayMember = "Yr"
            .ValueMember = "Yr"
        End With
        'obj.SelectedIndex = dsCommon.Tables("cmbYear").Rows.Count - 1
        obj.SelectedIndex = 0
    End Sub

    '## 신규 매출의 IncCd 구하기
    Public Function gFnGetIncCd() As String
        Dim sql As String
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim maxIncCd As String
        sql = "SELECT max(incCd)+1 as maxIncCd FROM tblIncome"
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "maxIncCd")

        'dbConn.Close()
        maxIncCd = dsCommon.Tables("maxIncCd").Rows(0).Item(0)

        Return maxIncCd
    End Function

    '## 제품별 재고 조사
    Public Sub gLookupInventory(ByVal prdCd As String, ByVal msgMode As String)

        If prdCd = "" Then
            MsgBox("제품코드를 바르게 입력해 주세요")
            Exit Sub
        End If
        Dim restCnt As String = "0"
        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim rtnMsg, sql As String
        sql = "SELECT sum([input])-sum([output]) as rest FROM tblPrdInOut WHERE prdCd=" + prdCd + " AND flag='Y'"

        Call dbConnection()
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "PrdInventory")


        If IsDBNull(dsCommon.Tables("PrdInventory").Rows(0).Item(0)) = False Then
            restCnt = dsCommon.Tables("PrdInventory").Rows(0).Item(0)
        End If

        sql = "UPDATE tblProduct SET inventoryCnt=" + restCnt + " WHERE prdCd=" + prdCd
        rtnMsg = "재고 조사가 완료 되었습니다."
        oleCmd = New OleDb.OleDbCommand(sql, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            dbConn.Close()
            If msgMode = "" Then
                MsgBox(rtnMsg)
            End If
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(sql)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 제품 단가 구하는 함수
    Public Function gFnUnitPrice(ByVal BCFlag As String, ByVal prdCd As String) As String

        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim sql, unitPrice As String
        sql = "SELECT unit" + BCFlag + "Price FROM tblProduct WHERE prdCd=" + prdCd
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "prdUintPrice")

        unitPrice = dsCommon.Tables("prdUintPrice").Rows(0).Item(0)
        unitPrice = Format(CInt(unitPrice), "#,##0")

        Return unitPrice
    End Function

    '## 박스당 갯수 구하는 함수
    Public Function gFnEaOnBox(ByVal qty As String, ByVal prdCd As String) As String

        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim sql, eaOnBox As String
        sql = "SELECT box2Ea FROM tblProduct WHERE prdCd=" + prdCd
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(sql, dbConn)
        daCommon.Fill(dsCommon, "prdEaOnBox")

        eaOnBox = dsCommon.Tables("prdEaOnBox").Rows(0).Item(0)
        eaOnBox = CInt(qty) * CInt(eaOnBox)
        eaOnBox = Format(CInt(eaOnBox), "#,##0")

        Return eaOnBox
    End Function

    '## 쿼리에 대한 하나의 값 반환하기
    Public Function gAFieldValue(ByVal query As String) As String

        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter
        Dim rtnValue As String
        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(query, dbConn)
        daCommon.Fill(dsCommon, "aFieldValue")

        'dbConn.Close()
        rtnValue = dsCommon.Tables("aFieldValue").Rows(0).Item(0)

        Return rtnValue
    End Function

    '## 쿼리를 저장하는 함수
    Public Sub gSubSaveAction(ByVal query As String, ByVal msg As String)

        Dim oleCmd As OleDb.OleDbCommand
        Dim oleTran As OleDb.OleDbTransaction
        oleCmd = New OleDb.OleDbCommand(query, dbConn)

        oleTran = dbConn.BeginTransaction
        oleCmd.Transaction = oleTran
        Try

            oleCmd.ExecuteNonQuery()
            oleTran.Commit()

            If msg <> "" Then
                MsgBox(msg)
            End If
        Catch err As OleDb.OleDbException
            oleTran.Rollback()
            dbConn.Close()
            MsgBox(query)

            MsgBox(err.Message, MsgBoxStyle.Critical, "오류")
        End Try
    End Sub

    '## 쿼리에 대한 DataTable 반환하기
    Public Function gMkDataTable(ByVal query As String, ByVal tbname As String) As DataTable

        Dim dsCommon As DataSet
        Dim daCommon As OleDb.OleDbDataAdapter

        dsCommon = New DataSet
        daCommon = New OleDb.OleDbDataAdapter(query, dbConn)
        daCommon.Fill(dsCommon, tbname)

        Return dsCommon.Tables(tbname)
    End Function

    '## O/S에 따른 Icon 표시 선택
    Public Sub gSubIconShow(ByVal obj As Object)

        Dim os As OperatingSystem = Environment.OSVersion
        If Mid(os.ToString, 1, 22) <> "Microsoft Windows NT 5" Then
            obj.ShowIcon = True
            obj.Icon = My.Resources.inven
        End If
    End Sub
End Module
